# -- coding:utf-8 --
import os
import pymysql
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
from dbutils.pooled_db import PooledDB
from urllib.parse import quote_plus as urlquote


def find_file_name(dir_path):
    """
    通过目录找出12个csv文件路径
    :param dir_path:
    :return:
    """
    list_file = os.listdir(dir_path)
    algo_config = None
    da_voltage_data_temp = None
    dv_voltage_channel = None
    dv_voltage_line_group_channel = None
    dv_voltage_line_group = None
    fs_voltage_transformer = None

    err_voltage_dynamic_slide_window = None
    err_voltage_error_pile = None
    err_voltage_matrix_status = None
    err_voltage_pre_pile_record = None
    err_voltage_slide_window = None
    err_voltage_error = None
    # 根据关键词查找csv文件名
    for file_name in list_file:
        if "algo_config" in file_name:
            algo_config = dir_path + "\\" + file_name
        elif "da_voltage_data_temp" in file_name:
            da_voltage_data_temp = dir_path + "\\" + file_name
        elif "dv_voltage_channel" in file_name:
            dv_voltage_channel = dir_path + "\\" + file_name
        elif "dv_voltage_line_group_channel" in file_name:
            dv_voltage_line_group_channel = dir_path + "\\" + file_name
        elif "dv_voltage_line_group" in file_name:
            dv_voltage_line_group = dir_path + "\\" + file_name
        elif "fs_voltage_transformer" in file_name:
            fs_voltage_transformer = dir_path + "\\" + file_name
        elif "err_voltage_dynamic_slide_window" in file_name:
            err_voltage_dynamic_slide_window = dir_path + "\\" + file_name
        elif "err_voltage_error_pile" in file_name:
            err_voltage_error_pile = dir_path + "\\" + file_name
        elif "err_voltage_matrix_status" in file_name:
            err_voltage_matrix_status = dir_path + "\\" + file_name
        elif "err_voltage_pre_pile_record" in file_name:
            err_voltage_pre_pile_record = dir_path + "\\" + file_name
        elif "err_voltage_slide_window" in file_name:
            err_voltage_slide_window = dir_path + "\\" + file_name
        elif "err_voltage_error" in file_name:
            err_voltage_error = dir_path + "\\" + file_name
    list_file_name = [algo_config, da_voltage_data_temp, dv_voltage_channel, dv_voltage_line_group_channel,
                      dv_voltage_line_group, fs_voltage_transformer, err_voltage_dynamic_slide_window,
                      err_voltage_error_pile, err_voltage_matrix_status, err_voltage_pre_pile_record,
                      err_voltage_slide_window, err_voltage_error]
    return list_file_name


def insert_data(csv_path, engine, table_name):
    """
    根据csv文件路径导入到SQL中
    :param csv_path:
    :param engine:
    :param table_name:
    :return:
    """
    pd_data = pd.read_csv(csv_path)
    # print(pd_data)
    # 使用drop方法删除第一列
    pd_data.drop(pd_data.columns[0], axis=1, inplace=True)
    pd_data.to_sql(table_name, engine, index=False, if_exists='append')
    print("添加{}表完毕".format(table_name))


def csv_to_sql(ip, dir_path):
    """
    根据csv文件夹路径，自动寻找文件、截断表、导入数据到mysql
    :param p_datetime:
    :param v_list:
    :param time:
    :param v_220_all_ty:
    :return:
    """
    list_file_name = find_file_name(dir_path)
    algo_config, da_voltage_data_temp, dv_voltage_channel, dv_voltage_line_group_channel, dv_voltage_line_group, \
        fs_voltage_transformer, err_voltage_dynamic_slide_window, err_voltage_error_pile, err_voltage_matrix_status, \
        err_voltage_pre_pile_record, err_voltage_slide_window, err_voltage_error = list_file_name
    POOL = PooledDB(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                    maxusage=None, setsession=[], ping=0, host=ip, port=3306, user='oetsky', password='Oetsky@123',
                    database='cvt_client_prod', charset='utf8')
    conn = POOL.connection()
    cursor = conn.cursor()
    # 截断数据表
    cursor.execute("TRUNCATE algo_config;")
    cursor.execute("TRUNCATE da_voltage_data_temp;")
    cursor.execute("TRUNCATE dv_voltage_channel;")
    cursor.execute("TRUNCATE dv_voltage_line_group;")
    cursor.execute("TRUNCATE dv_voltage_line_group_channel;")
    cursor.execute("TRUNCATE fs_voltage_transformer;")

    cursor.execute("TRUNCATE err_voltage_dynamic_slide_window;")
    cursor.execute("TRUNCATE err_voltage_error;")
    cursor.execute("TRUNCATE err_voltage_error_pile;")
    cursor.execute("TRUNCATE err_voltage_matrix_status;")
    cursor.execute("TRUNCATE err_voltage_pre_pile_record;")
    cursor.execute("TRUNCATE err_voltage_slide_window;")
    print("已清空半年数据表")
    cursor.close()
    conn.close()

    # 根据err_voltage_error数据表，提取开始和结束计算时间
    pd_err_voltage_error = pd.read_csv(err_voltage_error)
    min_time = np.min(pd_err_voltage_error["data_start_time"]) + " 00:00:00"
    max_time = np.max(pd_err_voltage_error["data_end_time"])
    print(min_time)
    print(max_time)

    userName = "oetsky"
    password = "Oetsky@123"
    dbHost = ip
    dbPort = 3306
    dbName = 'cvt_client_prod'
    DB_CONNECT = f'mysql+pymysql://{userName}:{urlquote(password)}@{dbHost}:{dbPort}/{dbName}?charset=utf8'
    engine = create_engine(
        DB_CONNECT,
        max_overflow=50,  # 超过连接池大小外最多创建的连接
        pool_size=50,  # 连接池大小
        pool_timeout=5,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1,  # 多久之后对线程池中的线程进行一次连接的回收（重置）
        # encoding='utf-8',
        echo=False
    )

    # 注入数据
    insert_data(algo_config, engine, "algo_config")
    insert_data(da_voltage_data_temp, engine, "da_voltage_data_temp")
    insert_data(dv_voltage_channel, engine, "dv_voltage_channel")
    insert_data(dv_voltage_line_group_channel, engine, "dv_voltage_line_group_channel")
    insert_data(dv_voltage_line_group, engine, "dv_voltage_line_group")
    insert_data(fs_voltage_transformer, engine, "fs_voltage_transformer")
    return min_time, max_time


if __name__ == '__main__':
    ip = "192.168.31.13"
    dir_path = r"E:\汤嘉提供\算法测试\通用算法\2V0_互感器档案信息01"
    # list_file_name = find_file_name(dir_path)
    min_time, max_time = csv_to_sql(ip, dir_path)
